package db;

import model.*;

import java.sql.*;
import java.util.ArrayList;

/**
 * DBEmployee.java
 * @author kbh
 * @version 5. oktober 2006, 2011.02.16
 *
 */
public class DBCaseXEmployee{
    private  Connection con;
    private DBCase dbCase;
    private DBEmployee dbEmployee;
    
    /** Creates a new instance of DBEmployee */
    public DBCaseXEmployee() {
      con = DBConnection.getInstance().getDBcon();
    }
    
    //Implements the methods from the interface
    // get all CaseXEmployeeGroups
    /*public ArrayList<CaseXEmployee> getAllCaseXEmployees(boolean _retriveAssociation)
    {
        return miscWhere("", _retriveAssociation);
    }
    //get one CaseXEmployeeGroup having the group type
    public CaseXEmployee getCaseXEmployeeCpr(String _cpr, boolean _retriveAssociation)
    {   String wClause = "  cpr = '" + _cpr + "'";
        return singleWhere(wClause, _retriveAssociation);
    }*/
    //find one CaseXEmployeeGroup having the id
    public boolean ifAssigned(String _cpr, String _id, boolean _retriveAssociation)
    {
    	String wClause = "  employeeCpr = '" + _cpr + "'" + "and caseId = '" + _id + "'";
        return singleWhere(wClause, _retriveAssociation);
    }
    
    public ArrayList<Case> getCaseList(String _cpr, boolean _retriveAssociation)
    {
    	String wClause = "  employeeCpr = '" + _cpr + "'";
    	return miscWhereCase(wClause, _retriveAssociation);
    }
  
     //insert a new CaseXEmployee group
    public int insertCaseXEmployee(Employee _emp, Case _case) throws Exception
    {  
       int rc = -1;
	   String query="INSERT INTO CaseXEmployee(employeeCpr, caseId)  VALUES('"+
			   _emp.getCpr() + "','" +
			   _case.getId() + "')";
                     

       System.out.println("insert : " + query);
      try{ // insert new employee +  dependent
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          System.out.println("CaseXEmployee ikke oprettet");
          throw new Exception ("CaseXEmployee is not inserted correctly");
       }
       return(rc);
    }

	public int updateCaseXEmployee(Employee _emp, Case _case, Employee _emp2, Case _case2)
	{
		int rc=-1;

		String query="UPDATE CaseXEmployee SET "+
				"caseId ='"+ _case2.getId()+"', "+
				"employeeCpr ='"+ _emp2.getCpr()+"', "+
		          " WHERE employeeCpr = '"+ _emp.getCpr()+"', " +
		          " and caseId = '"+ _case.getId() + "'";
                System.out.println("Update query:" + query);
  		try{ // update employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in CaseXEmployee db: "+ex);
	  	}
		return(rc);
	}
	
	public int deleteCaseXEmployee(Employee employee, Case thecase)
	{
               int rc=-1;
	  
	  	String query="DELETE FROM CaseXEmployee WHERE employeeCpr = '" +
				employee.getCpr() + "' AND caseId = '" + thecase.getId() + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	rc = stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in CaseXEmployee db: "+ex);
   	        }
		return(rc);
	}
	
	public void deleteCaseAssociations(String _caseId)
	{
		String query="DELETE FROM CaseXEmployee WHERE caseId = '" + _caseId + "'";
                System.out.println(query);
	  	try{ // delete from employee
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	  	stmt.executeUpdate(query);
	 	  	stmt.close();
  		}//slut try	
   	        catch(Exception ex){
	 	  	System.out.println("Delete exception in CaseXEmployee db: "+ex);
   	        }
	}
	
	//private methods
    //michWere is used whenever we want to select more than one employee
	
	 
	private boolean singleWhere(String wClause, boolean retrieveAssociation)
	{
        ResultSet results = null;
	    
	    String query =  buildQuery(wClause);
	    System.out.println(query);
	    
        try
        { // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	 	if(results.next())
	 		return true;
	 	else
	 		return false;
    	}//slut try	
	 	catch(Exception e)
	 	{
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
			return false;
	 	}
	}
	
	private ArrayList<Case> miscWhereCase(String wClause, boolean retrieveAssociation)
	{
        ResultSet results;
	    ArrayList<Case> list = new ArrayList<Case>();
	    
	    String query =  buildQuery(wClause);

            try{ // read the employee from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
	
		while( results.next() ){
	     	Case cases = new Case();
	     	cases = dbCase.buildCase(results);	
                 list.add(cases);	
		}//end while
                 stmt.close();     
                 /*if(retrieveAssociation)
                 {   //The supervisor and department is to be build as well
                     for(CaseXEmployee CaseXEmployee : list){
                        String cpr = CaseXEmployee.getContact().getCpr();
                        Contact contact = dbContact.getContactCpr(cpr,false);
                        CaseXEmployee.setContact(contact);
                        System.out.println("Contact is seleceted");
                     }
                 }//end if */  
			
		}//slut try	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	//Singelwhere is used when we only select one employee 	
	/*private CaseXEmployee singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		CaseXEmployee CaseXEmployee = new CaseXEmployee();
                
	        String query =  buildQuery(wClause);
                System.out.println(query);
		try{ // read the CaseXEmployeeGroup from the database
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 		results = stmt.executeQuery(query);
	 		
	 		if( results.next() ){
                            CaseXEmployee = buildCaseXEmployee(results);
                            //assocaition is to be build
                            stmt.close();
                           /* if(retrieveAssociation)
                            {   //The supervisor and department is to be build as well
                                String cpr = CaseXEmployee.getContact().getCpr();
                                Contact contact = dbContact.getContactCpr(cpr,false);
                                CaseXEmployee.setContact(contact);
                                System.out.println("Contact is seleceted");
                            
                            }//end if */ 
			/*}
                        else{ //no employee was found
                            CaseXEmployee = null;
                        }
		}//end try	
	 	catch(Exception e){
	 		System.out.println("Query exception: "+e);
	 	}
		return CaseXEmployee;
	}*/
	//method to build the query
	private String buildQuery(String wClause)
	{
	    String query="SELECT employeeCpr, caseId  FROM CaseXEmployee";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	//method to build an employee object



 
}  
    

